---
title: Retrieve the service team and derive email addresses
summary: Loads the assigned service-team members for a policy and resolves their email addresses from Oracle and SQL Server (BDS) sources.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-09-01
library: SAIF.Business.View.Policy
reference_type: embedded
operation_id: op-05-retrieve-service-team
trigger: Consumer reads TeamAssignmentCollection
---

# Retrieve the service team and derive email addresses

Loads the team of SAIF staff assigned to service a policy (underwriter, loss-control consultant, etc.) and resolves each member's email address, which may require a cross-database (BDS/SQL Server) lookup.

| Field | Value |
| --- | --- |
| **Operation ID** | op-05-retrieve-service-team |
| **Trigger** | Consumer reads `TeamAssignmentCollection` |
| **Inputs** | Policy number, optional `inMaskData` flag |
| **Outputs** | `clsTeamAssignmentCollection` |
| **Primary components** | `clsTeamAssignmentCollection.GetTeamAssignmentCollection`, `InitializeEmailAddress`, `clsTeamAssignment.InitializeEmailAddress` |
| **Entry point** | `SAIF.Business.View.Policy/clsTeamAssignmentCollection.vb` |

## Steps

1. Caller invokes `GetTeamAssignmentCollection(inDataportal, inPolicyNum[, inMaskData])`, which constructs a `Criteria` and delegates to `inDataportal.DataAccessLayer.RetrieveObject`. A second overload additionally accepts a SQL Server data portal but delegates identically to the first ([SQL-031](../sql/SQL-031-team-assign-query.md), procedure `pk72846_view_policy.team_assign_query`).
2. Framework invokes `BeforeRetrieve`: creates a new `IDataRequest`, assigns `MyCriteria.RetrieveSQL`, sets `CommandType = StoredProcedure`, and binds parameters (policy number and mask-data flag from the criteria).
3. Framework executes the request against Oracle.
4. Framework invokes `AfterRetrieve`: loops `Reader.Read`, and for each row constructs a `New clsTeamAssignment`, sets `TeamAssignment.MaskData = MyCriteria.MaskData` ([BR-VP-010](../business-rules.md#br-vp-010-demo-users-see-masked-data)), calls `TeamAssignment.Load(Reader)`, and adds it via `MyBase.Add`.
5. `TeamFunctionToCode`/`CodeToTeamFunction` translate between the `TeamFunctions` enum (e.g. `LossControlConsultant`, `Underwriter`, `AssignedRiskCustServRep`) and the short database codes (`"LCR"`, `"UW"`, `"ARP"`, etc.), including an explicit `Unknown` fallback for unrecognized codes.
6. Once populated, callers may invoke `InitializeEmailAddress(inSQLServerDataPortal)` (or the obsolete misspelled `InitializeEmaillAddress`, or the "using system user" variant) to loop every `clsTeamAssignment` in the collection and call `ta.InitializeEmailAddress(inSQLServerDataPortal)` per member ([BR-VP-022](../business-rules.md#br-vp-022-team-member-email-address-derivation)).
7. `clsTeamAssignment.InitializeEmailAddress` (not directly inspected in this pass) is understood from the hub catalog and constructor signatures to resolve the member's email by querying external-client/registrant tables — `cp_external_client_lookup` on the SQL Server BDS connection ([SQL-029](../sql/SQL-029-cp-external-client-lookup.md)) and/or `sp73341_external_client_lookup` on Oracle ([SQL-030](../sql/SQL-030-sp73341-external-client-lookup.md)).*

## Process flow

```mermaid
flowchart TD
	Start(["Consumer reads<br>TeamAssignmentCollection"]) --> A["GetTeamAssignmentCollection<br>constructs Criteria"]
	A --> B["BeforeRetrieve assigns<br>team_assign_query, binds<br>policy num / mask flag"]
	B --> C["Framework executes<br>procedure against Oracle"]
	C --> D["AfterRetrieve reads rows"]
	D --> E{More rows?}
	E -->|Yes| F["New clsTeamAssignment,<br>set MaskData, Load, Add"]
	F --> E
	E -->|No| G["Return populated<br>clsTeamAssignmentCollection"]
	G --> H["Caller invokes<br>InitializeEmailAddress"]
	H --> I["Per member: resolve email via<br>cp_external_client_lookup (BDS) or<br>sp73341_external_client_lookup (Oracle)"]
```

## Failure and edge-case behavior
- Email resolution is a separate, opt-in step (`InitializeEmailAddress`) — a caller that only reads `TeamAssignmentCollection` without calling it will see team members with unresolved email addresses.*
- `InitializeEmaillAddress` (the misspelled overload) is marked `<Obsolete>` but still delegates functionally to the same email-initialization logic as `InitializeEmailAddress` — both remain callable.
- `CodeToTeamFunction` returns `TeamFunctions.Unknown` for any database code it does not recognize, rather than raising, which downstream consumers must handle explicitly.

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

## Sources
- `SAIF.Business.View.Policy/clsTeamAssignmentCollection.vb`
- `SAIF.Business.View.Policy/clsTeamAssignment.vb`